<?php

namespace App\Http\Controllers\VehicleAccounting;

use App\Http\Controllers\Controller;
use App\Models\Accounting\AccountingImportData;
use App\Models\Accounting\AccountingLineNote;
use App\Models\Accounting\AccountingRate;
use App\Models\Accounting\AccountingReport;
use App\Models\Accounting\AccountingReportFields;
use App\Models\Charge\Charge;
use App\Models\MaintainRepair\MrApply;
use App\Models\Material\TyreOutMaterial;
use App\Models\Mileage\Mileage;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Exception;

class LineAccountingController extends Controller
{
    /**
     * 单车核算
     *
     * @param  Request  $request
     *
     * @return mixed
     */
    public function lineAccounting(Request $request)
    {
        $params = $request->all();
        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
        } else {
            $params['date'] = date('Y-m-01');
        }
        $lineSum = $this->getlineAccounting($params);
        return response()->formatJson(['result' => $lineSum, 'date' => $params['date'],]);
    }

    public function getlineAccounting($params)
    {
        $endDate = date('Y-m-t', strtotime($params['date']));
        $rateRecord = AccountingRate::query()
            ->where('start', '>=', $params['date'])
            ->where('end', '<=', $params['date'])
            ->first();
        $rate = $rateRecord ? $rateRecord->rate : 0;

        $resultIncome = $this->vehicleTransData($params)->get()->groupBy('线路')->all();
        $result = [];
        foreach ($resultIncome as $line => $v) {
            $cash = $v->sum('现金收入');
            $card = $v->sum('一卡通收入');
            $post = $v->sum('邮储收入');
            $fixed = $v->sum('包车收入');
            $result[$line] = [
                'line'              => $line,
                'license_plate_num' => $v->pluck('车牌号')->toArray(),
                'business_income'   => number_format(($cash + $card + $post + $fixed), 2, '.', ''),
            ];
        }

        // 备注
        $busNote = AccountingLineNote::query()->where('month', $params['date'])->get()->toArray();
        foreach ($busNote as $k => $v) {
            foreach ($result as $kk => &$vv) {
                if ((int) $v['line'] === $kk) {
                    $vv['note'] = $v['note'];
                }
            }
        }

        // 逸华加气
        $resultYh = DB::table('v_gas')
            ->where('类型', '>', 0)
            ->whereBetween('日期', [$params['date'].' 00:00:00', $endDate.' 23:59:59'])
            ->get()
            ->groupBy('线路')
            ->all();
        if ($resultYh) {
            foreach ($resultYh as $k => $v) {
                foreach ($result as $kk => &$vv) {
                    if ($k === $kk) {
                        $vv['m_air_yihua'] = number_format($v->sum('金额'), 2, '.', '');
                    }
                }
            }
        }

        // 顺康加气
        $month = $params['date'];
        $resultSk = DB::table('v_gas_sk')
            ->whereBetween('日期', [date('Y-m-t 08:00:00', strtotime("$month -1 month")), $endDate.' 07:59:59'])
            ->get()
            ->groupBy('线路')
            ->all();
        if ($resultSk) {
            foreach ($resultSk as $k => $v) {
                foreach ($result as $kk => &$vv) {
                    if ($k === $kk) {
                        $vv['monthly_cost'] = number_format($v->sum('金额'), 2, '.', '');
                    }
                }
            }
        }

        // 里程记录
        $millage = Mileage::query()->whereBetween('date', [$params['date'], $endDate])->get();

        // 修车记录
        $applyQuery = MrApply::query()->whereBetween('date', [$params['date'], $endDate])
            ->with([
                'process' => function ($query) {
                    // 查询已结束的流程
                    $query->where('apply_key', MrApply::getTableName())->where('status', 0);
                },
            ])
            ->with('detail')->get();

        // 轮胎出库记录
        $tyreQuery = TyreOutMaterial::query()->whereBetween(
            'out_time',
            [$params['date'].' 00:00:00', $endDate.' 23:59:59']
        )
            ->with([
                'process' => function ($query) {
                    // 查询已结束的流程
                    $query->where('apply_key', TyreOutMaterial::getTableName())->where('status', 0);
                },
            ])
            ->get();

        // 获取所有数据
        $dataQuery = AccountingImportData::query();
        if (isset($params['line']) && $params['line'] !== '-1') {
            $dataQuery->where('line', '=', $params['line']);
        }

        $data = $dataQuery->where('month', $params['date'])->get()->groupBy('line')->toArray();

        // 获取数据对应关系
        $reportId = AccountingReport::query()->where('report_name', '单车核算统计')->first()->id;
        $fields = [];
        $parent = AccountingReportFields::query()
            ->where('report_id', $reportId)
            ->where('parent_id', 0)
            ->orderBy('sort')
            ->get()
            ->toArray();

        foreach ($parent as $v) {
            $son = AccountingReportFields::query()
                ->where('report_id', $reportId)
                ->where('parent_id', $v['id'])
                ->orderBy('sort')
                ->get()
                ->toArray();

            if (! $son) {
                $fields[] = $v;
            }
            foreach ($son as $key => $value) {
                $value['parent'] = $v['header_name'];
                $fields[] = $value;
            }
        }
        // 匹配数据
        $resultData = [];
        $ignoreKey = ['车牌号', '线路', '备注', '类别'];
        foreach ($data as $name => $v) {
            $part = [];
            foreach ($fields as $key => $item) {
                if ($item['header_name'] === '线路') {
                    $part[$item['header_name']] = $name;
                } else {
                    if ($item['table_name']) {
                        $sign = collect($v)->where('table_name', $item['table_name'])->first();
                        if ($sign) {
                            $signData = $sign['data'];
                            if (in_array($item['column_name'], $ignoreKey, true)) {
                                $part[$item['header_name']] = json_decode($signData, true)[$item['column_name']];
                            } else {
                                $part[$item['header_name']] = number_format(
                                    json_decode($signData, true)[$item['column_name']],
                                    2,
                                    '.',
                                    ''
                                );
                            }
                        } else {
                            $part[$item['header_name']] = '';
                        }
                    } else {
                        $part[$item['header_name']] = '';
                    }
                }
            }
            $resultData[$name] = $part;
        }

        foreach ($result as $key => $v) {
            // 趟次
            $lineCount = collect($millage)->where('line', $key);
            if ($lineCount) {
                $planCount = $lineCount->sum('plan_times');
                $dropCount = $lineCount->sum('drop_off');
                $result[$key]['num'] = $planCount - $dropCount;
                $result[$key]['monthly_km'] = number_format($lineCount->sum('gps_mileage'), 2, '.', '');
            } else {
                $result[$key]['num'] = 0;
                $result[$key]['monthly_km'] = 0;
            }
            // 含税实际收入
            $result[$key]['business_income'] = $v['business_income'];
            // 入账主营收入
            $result[$key]['bookedIncome'] = $v['business_income'] * (1 - ($rate / 100));
            // 修理费
            $apply = $applyQuery->where('line', $key)->toArray();
            if ($apply) {
                $detail = array_column($apply, 'detail');
                $result[$key]['repair'] = $detail ? array_sum(array_column($detail, 'amount')) : 0;
            } else {
                $result[$key]['repair'] = 0;
            }
            // 轮胎
            $tyre = $tyreQuery
                ->load([
                    'tyreOutMaterialDetail' => function ($query) use ($key) {
                        // 查询已结束的流程
                        $query->where('line', $key);
                    },
                ])->toArray();
            if ($tyre) {
                $detail = array_column($tyre, 'tyreOutMaterialDetail');
                $amount = 0;
                if ($detail) {
                    foreach ($detail as $vv) {
                        $amount += $vv['amount'] * $vv['price'];
                    }
                }
                $result[$key]['tire'] = $amount;
            } else {
                $result[$key]['repair'] = 0;
            }
            //充电记录
            $start_date = date('Y-m-d 00:00:00', strtotime($params['date']));
            $end_date = date('Y-m-d 23:59:59', strtotime($endDate));
            $chargeQuery = Charge::query()
                ->select(DB::raw('SUM(trading_power) as total_trading_power'))
                ->where('line', $v['line'])
                ->whereBetween('charging_end_at', [$start_date, $end_date])
                ->get()
                ->toArray();
            $result[$key]['total_trading_power'] = $chargeQuery[0]['total_trading_power'] ?: 0;
        }

        foreach ($result as $kkk => $vvv) {
            foreach ($resultData as $kkkk => $vvvv) {
                if ($vvvv['线路'] === (int) $vvv['line']) {
                    $result[$kkk]['premium'] = $vvvv['保险费'];
                    $result[$kkk]['cost'] = $vvvv['费用'];
                    $result[$kkk]['salary'] = $vvvv['工资'];
                    $result[$kkk]['depreciation'] = $vvvv['折旧'];
                    $result[$kkk]['check'] = $vvvv['洗车、例检'];
                    $result[$kkk]['insurance'] = $vvvv['五险'];
                    $result[$kkk]['other'] = $vvvv['其他'];
                    $result[$kkk]['taxes'] = $vvvv['营业税金及附加'];
                    $count = $vvvv['保险费'] + $vvvv['费用'] + $vvvv['折旧'] + $vvvv['洗车、例检'] + $vvvv['五险'] + $vvvv['其他'] + $vvvv['营业税金及附加'];
                    $result[$kkk]['importData'] = $count ? number_format($count, 2, '.', '') : 0;
                }
            }
        }

        foreach ($result as $kkk => $vvv) {
            $importData = array_key_exists('importData', $vvv) ? $vvv['importData'] : 0;
            $salary = array_key_exists('salary', $vvv) ? $vvv['salary'] : 0;
            $monthlyCost = array_key_exists('monthly_cost', $vvv) ? $vvv['monthly_cost'] : 0;
            $mAirYihua = array_key_exists('m_air_yihua', $vvv) ? $vvv['m_air_yihua'] : 0;
            $repair = array_key_exists('repair', $vvv) ? $vvv['repair'] : 0;
            $tire = array_key_exists('tire', $vvv) ? $vvv['tire'] : 0;

            $subtotal = $salary + $monthlyCost + $mAirYihua + $repair + $tire + $importData;
            $result[$kkk]['subtotal'] = $subtotal ? number_format($subtotal, 2, '.', '') : 0;

            $taxes = array_key_exists('taxes', $vvv) ? $vvv['taxes'] : 0;
            $profits = $vvv['business_income'] - $taxes - $subtotal;
            $result[$kkk]['profits'] = $profits ? number_format($profits, 2, '.', '') : 0;
        }

        // 单线核算
        $lineSum = [];
        if ($result) {
            $keys = [
                'monthly_km',
                'num',
                'business_income',
                'bookedIncome',
                'salary',
                'total_trading_power',
                'monthly_cost',
                'm_air_yihua',
                'repair',
                'tire',
                'premium',
                'cost',
                'depreciation',
                'check',
                'insurance',
                'other',
                'subtotal',
                'taxes',
                'profits',
            ];
            $lineData = collect($result)->groupBy('line')->toArray();
            foreach ($lineData as $line => $item) {
                $lineSum[$line]['line'] = $line === 0 ? '停运' : $line;
                $lineSum[$line]['note'] = implode(',', array_column($item, 'note'));
                foreach ($keys as $k => $v) {
                    $sum = array_sum(array_column($item, $v));
                    $lineSum[$line][$v] = $sum ? number_format($sum, 2, '.', '') : 0;
                }
            }
        }
        $lineSum = $this->rebuildKeys($lineSum);
        $lineSum = collect($lineSum)->sortBy('line')->toArray();

        $sortResult = collect($lineSum)->sortByDesc('profits')->toArray();

        $sortResult = $this->rebuildKeys($sortResult);
        foreach ($lineSum as $k => $v) {
            foreach ($sortResult as $sort => $vv) {
                if ($vv['line'] === $v['line']) {
                    $lineSum[$k]['rank'] = $sort + 1;
                }
            }
        }
        $stop = [];
        foreach ($lineSum as $k => $v) {
            if ($v['line'] === '停运') {
                $stop = $lineSum[$k];
                unset($lineSum[$k]);
            }
        }
        $lineSum = $this->rebuildKeys($lineSum);
        if ($stop) {
            $lineSum[] = $stop;
        }
        return $lineSum;
    }

    public function vehicleTransData($params)
    {
        $query = DB::table('v_income');

        $query->whereBetween('日期', [$params['date'], date('Y-m-t', strtotime($params['date']))]);

        if (isset($params['line']) && $params['line'] !== '-1') {
            $query->where('线路', $params['line']);
        }

        return $query->orderBy('车牌号');
    }

    public function rebuildKeys($array)
    {
        if (! is_array($array)) {
            return $array;
        }
        $keys = implode('', array_keys($array));
        if (is_numeric($keys)) {
            $array = array_values($array);
        }
        $array = array_map([$this, 'rebuildKeys'], $array);

        return $array;
    }

    public function updateLineNote(Request $request)
    {
        $params = $request->all();
        $data = collect($params['updateRecords'])->pluck('note', 'line')->toArray();
        $date = date('Y-m-d', strtotime($params['date']));
        foreach ($data as $line => $note) {
            $result = AccountingLineNote::query()->updateOrCreate(
                [
                    'month' => $date,
                    'line'  => $line === '停运' ? 0 : $line,
                ],
                [
                    'note' => $note,
                ]
            );
        }
        $message = $result ? '修改成功' : '修改失败';

        return response()->formatJson($result, $message);
    }

    /**
     * 导出单线核算
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws Exception
     */
    public function exportLineAccounting(Request $request)
    {
        $params = $request->all();

        if (isset($params['date'])) {
            $params['date'] = date('Y-m-01', strtotime($params['date']));
        } else {
            $params['date'] = date('Y-m-01');
        }
        $data = $this->getlineAccounting($params);

        $date = date('Y-m-d', strtotime($params['date']));
        $endDate = date('Y-m-t', strtotime($params['date']));
        $title = '安康市公共交通责任有限公司单线核算表('.$date.'--'.$endDate.')';
        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maardten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', $title)
            ->setCellValue('A2', '线路')->setCellValue('B2', '车公里')->setCellValue('C2', '趟次')
            ->setCellValue('D2', '含税实际主营收入')->setCellValue('E2', '入账主营收入')
            ->setCellValue('F2', '成本项目')
            ->setCellValue('F3', '工资')->setCellValue('G3', '充电')->setCellValue('H3', '气耗')
            ->setCellValue('I3', '逸华加气')->setCellValue('J3', '修理费')
            ->setCellValue('K3', '轮胎')->setCellValue('L3', '保险费')
            ->setCellValue('M3', '费用')->setCellValue('N3', '折旧')->setCellValue('O3', '洗车、例检')
            ->setCellValue('P3', '五险')->setCellValue('Q3', '其他')->setCellValue('R3', '小 计')
            ->setCellValue('S2', '营业税金及附加')->setCellValue('T2', '实际主营利润')
            ->setCellValue('U2', '盈利亏损排列')->setCellValue('V2', '备注');

        $num = 4;
        $conut = count($data);
        for ($i = 0, $iMax = $conut; $i < $iMax; $i++) {
            $spreadsheet->getActiveSheet()->setCellValue('A'.$num, $data[$i]['line']);
            $spreadsheet->getActiveSheet()->setCellValue('B'.$num, $data[$i]['monthly_km'] ?: 0);
            $spreadsheet->getActiveSheet()->setCellValue('C'.$num, $data[$i]['num'] ?: 0);
            $spreadsheet->getActiveSheet()->setCellValue('D'.$num, $data[$i]['business_income'] ?: 0);
            $spreadsheet->getActiveSheet()->setCellValue('E'.$num, $data[$i]['bookedIncome'] ?: 0);
            $spreadsheet->getActiveSheet()->setCellValue('F'.$num,
                array_key_exists('salary', $data[$i]) ? $data[$i]['salary'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('G'.$num,
                array_key_exists('total_trading_power', $data[$i]) ? $data[$i]['total_trading_power'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('H'.$num,
                array_key_exists('monthly_cost', $data[$i]) ? $data[$i]['monthly_cost'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('I'.$num,
                array_key_exists('m_air_yihua', $data[$i]) ? $data[$i]['m_air_yihua'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('J'.$num,
                array_key_exists('repair', $data[$i]) ? $data[$i]['repair'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('K'.$num,
                array_key_exists('tire', $data[$i]) ? $data[$i]['tire'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('L'.$num,
                array_key_exists('premium', $data[$i]) ? $data[$i]['premium'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('M'.$num,
                array_key_exists('cost', $data[$i]) ? $data[$i]['cost'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('N'.$num,
                array_key_exists('depreciation', $data[$i]) ? $data[$i]['depreciation'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('O'.$num,
                array_key_exists('check', $data[$i]) ? $data[$i]['check'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('P'.$num,
                array_key_exists('insurance', $data[$i]) ? $data[$i]['insurance'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('Q'.$num,
                array_key_exists('other', $data[$i]) ? $data[$i]['other'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('R'.$num,
                array_key_exists('subtotal', $data[$i]) ? $data[$i]['subtotal'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('S'.$num,
                array_key_exists('taxes', $data[$i]) ? $data[$i]['taxes'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('T'.$num,
                array_key_exists('profits', $data[$i]) ? $data[$i]['profits'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('U'.$num,
                array_key_exists('rank', $data[$i]) ? $data[$i]['rank'] : 0);
            $spreadsheet->getActiveSheet()->setCellValue('V'.$num,
                array_key_exists('note', $data[$i]) ? $data[$i]['note'] : '');
            $num++;
        }
        $spreadsheet->getActiveSheet()->setCellValue('A'.($num + 1), '总合计:');
        //格式化数据为千分位
        $spreadsheet->getActiveSheet()->getStyle('B4:U'.($num + 1))
            ->getNumberFormat()->setFormatCode('#,##0.00');
        //写入计算公式
        $array = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U'];
        foreach ($array as $value) {
            $spreadsheet->getActiveSheet()->setCellValue($value.($num + 1), '=SUM('.$value.'4:'.$value.$num.')');
            $spreadsheet->getActiveSheet()->getCell($value.($num + 1))->getCalculatedValue();
        }
        // 合并行、列
        $spreadsheet->getActiveSheet()->mergeCells('A1:V1')->mergeCells('F2:R2')
            ->mergeCells('A2:A3')->mergeCells('B2:B3')->mergeCells('C2:C3')
            ->mergeCells('D2:D3')->mergeCells('E2:E3')->mergeCells('S2:S3')
            ->mergeCells('T2:T3')->mergeCells('U2:U3')->mergeCells('V2:V3')
            ->mergeCells('A'.$num.':V'.$num);

        //  设置宽度
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(15); // 默认列宽设置为12
        // 设置高度
        $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(38);
        $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(38);
        //居中
        $numberStyleCenter = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical'   => Alignment::VERTICAL_CENTER,
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1:V3')->applyFromArray($numberStyleCenter)
            ->getAlignment()->setWrapText(true);
        //设置字体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $spreadsheet->getActiveSheet()->getStyle('A2:V3')->getFont()->setBold(true);
        //加边框
        $borderStyleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color'       => ['argb' => '000000000'],
                ],
            ],
        ];
        $spreadsheet->getActiveSheet()->getStyle('A1:V'.($num + 1))->applyFromArray($borderStyleArray);
        // 重命名 worksheet
        $c = '单线核算'.$date.'-'.$endDate;
        $spreadsheet->getActiveSheet()->setTitle($c);
        // }
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$c.'.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}
